"""Place Data Table in SQLite.

.. helpdoc::

Places a Data table (R data.frame) into an SQLite database.  This widget places the data into the database where it will remain, even after the connection that created the table is removed.  

Users can use this widget to build up databases from R data.  
"""

"""<widgetXML>
    <name>
        Place Data Table in SQLite
    </name>
    <icon>
        readfile.png
    </icon>
    <summary>
        Place a Data table into an SQLite database.
    </summary>
    <tags>
        <tag priority="10">
            SQLite
        </tag>
    </tags>
    <author>
        <authorname>Kyle R. Covington</authorname>
        <authorcontact>kyle@red-r.org</authorcontact>
    </author>
    </widgetXML>
"""


from OWRpy import *
import redRGUI
import sqlite3 
import signals
import re, os, redR
class convertDataFrameToSQLite(OWRpy):
    def __init__(self, **kwargs):

        OWRpy.__init__(self, **kwargs)
        
        self.data = ''
        self.database = ''
        self.inputs.addInput('DataFrame', 'DataFrame', signals.base.RDataFrame, self.gotDataFrame)
        self.inputs.addInput('Database', 'Database', signals.RedRSQLite.SQLiteDatabase, self.gotDB)
        self.outputs.addOutput('sqltable', 'SQLite Table', signals.RedRSQLite.SQLiteTable)
        
        ###  GUI  ###  Should be very simple, just ask the user where she wants to place the database and what the table name should be.
        
        """.. rrgui::
            :description: `Location of the database.`
        """
        self.dbLabel = redRGUI.base.widgetLabel(self.controlArea, 'local|temp.db')
        
        #box = redRGUI.base.groupBox(self.controlArea, label = 'Database Location', orientation = 'horizontal')
        #button = redRGUI.base.button(box, 'Set Database', callback = self.changeDatabase)
        #button2 = redRGUI.base.button(box, 'Set Local', callback = self.setLocal)
        
        optionsBox = redRGUI.base.groupBox(self.controlArea, label = 'Options')
        grid = redRGUI.base.gridBox(optionsBox)
        
        self.RRSQLITEPARAM_append = redRGUI.base.TFCheckBox(grid.cell(0,0), label = 'Append')
        self.RRSQLITEPARAM_header = redRGUI.base.TFCheckBox(grid.cell(0,1), label = 'Header')
        self.RRSQLITEPARAM_rownames = redRGUI.base.TFCheckBox(grid.cell(1,0), label = 'Include Row Names')
        self.RRSQLITEPARAM_overwrite = redRGUI.base.TFCheckBox(grid.cell(1,1), label = 'Overwrite')
        
        self.tableNameLineEdit = redRGUI.base.lineEdit(self.controlArea, label = "Table Name")
        
        self.RRSQLITEPARAM_outputTable = redRGUI.base.textEdit(self.controlArea, label = 'Data Summary')
        
        commitButton = redRGUI.base.commitButton(self.bottomAreaRight, 'Commit', callback = self.commit)
    def gotDataFrame(self, data):
        if data:
            self.data = data
            self.status.setStatus(1)
        else:
            self.data = ''
            
    def gotDB(self, data):
        if data:
            self.database = data.getData()
            
        else:
            self.database = 'local|temp.db'
        self.dbLabel.setText(self.database)
    def commit(self):
        ## make a call in R so that we save the table
        if self.data == None or self.data == '':
            self.status.setStatus(0)
            return
        if unicode(self.tableNameLineEdit.text()) == '':
            self.status.setText('No table name entered')
            self.status.setStatus(0)
            return
        if self.database == '':
            self.status.setStatus(0)
            return
        if 'local|' in self.database:  # convert the database if the local name is present.      
            import redREnviron
            database = os.path.join(redREnviron.directoryNames['tempDir'], unicode(self.database.split('|')[1]))
        else:
            database = unicode(self.database)
            
        database = database.replace('\\','/')
        
        if not self.require_librarys(['RSQLite']):
            QMessageBox.information(self, 'SQLite Conversion','Can\'t load the RSQLite package.  Please connect to the internet to download.',  QMessageBox.Ok + QMessageBox.Default)
            return
        #self.R('m<-dbDriver("SQLite")', wantType = redR.NOCONVERSION)
        d = {'TABLE':unicode(self.tableNameLineEdit.text()), 'DATABASE':database, 'APPEND': self.RRSQLITEPARAM_append.checked(), 'OVERWRITE':self.RRSQLITEPARAM_overwrite.checked(), 'VALUE':unicode(self.data.getData()), 'HEADER': self.RRSQLITEPARAM_header.checked(), 'ROWNAMES': self.RRSQLITEPARAM_rownames.checked()}
        self.R('con<-dbConnect("SQLite", dbname=\'%(DATABASE)s\')' % d, wantType = redR.NOCONVERSION)
        self.R('dbWriteTable(con, \'%(TABLE)s\', as.data.frame(%(VALUE)s), overwrite = %(OVERWRITE)s, append = %(APPEND)s, row.names = %(ROWNAMES)s, header = %(HEADER)s)' % d, wantType = redR.NOCONVERSION)
        
        """.. helpdoc::
Data is summarized in the Data Summary area when the table is committed.  This will be the first 10 lines of the table.
        """
        
        self.RRSQLITEPARAM_outputTable.clear()
        self.RRSQLITEPARAM_outputTable.insertPlainText("A summary of your data is shown below.\n\n" + self.R('paste(capture.output(dbGetQuery(con, "SELECT * FROM %(TABLE)s LIMIT 10")), collapse = "\n")' % d, wantType = redR.CONVERT))
        
        
        self.R('dbCommit(con)', wantType = redR.NOCONVERSION)
        self.R('dbDisconnect(con)', wantType = redR.NOCONVERSION)  # close the connection
        
        newData = signals.RedRSQLite.SQLiteTable(self, data = unicode(self.tableNameLineEdit.text()), database = self.database)
        
        self.rSend('sqltable', newData)
        
        self.status.setText('Data Committed')
        